package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Comment;

public class DBComment implements IFDBComment {
	private Connection con;
	private static DBComment dbCom;

	private DBComment() {
		con = DBConnection.getInstance().getDBcon();
	}
	
	public static DBComment getInstance(){
		if(dbCom==null) dbCom = new DBComment();
		return dbCom;
	}
	
	public ArrayList<Comment> getAllComments(boolean forPeople) {
		return miscWhere("",forPeople);
	}

	public ArrayList<Comment> getComments_byID(int pid,boolean forPeople) {
		String pORa = "cp.pid";
		if(!forPeople) pORa = "apartId";
		return miscWhere(pORa+"="+pid,forPeople);
	}
	
	public Comment getComment_byID(int cid) {
		return singleWhere("cid="+cid,false);
	}

	public int insert(Comment comments,boolean forPeople) {
		int rc = -1;
		String query = "INSERT INTO Comment (comment,date,author) VALUES ('"+ comments.getComment() + "','"+comments.getDate()+"',"+comments.getAuthorId()+")";
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query,Statement.RETURN_GENERATED_KEYS);
			
			ResultSet rs = stmt.getGeneratedKeys();
	        if (rs.next())
	        {
	        	rc = rs.getInt(1);
	        	
	        	String pORa  = "CommentPeople";
	        	String pORa2 = "cp.pid";
	    		if(!forPeople)
	    		{
	    			pORa  = "CommentApartment";
	    			pORa2 = "apartId";
	    		}
	        	
	        	String query2 = "INSERT INTO "+pORa+" (cid,"+pORa2+") VALUES ("+rc+","+comments.getId()+")";
	        	rc = stmt.executeUpdate(query2);
	        }
	        rs.close();
			
			stmt.close();
		} catch (Exception e) {
			System.err.println("Could not insert Comment: " + e.getMessage());
		}
		
		
		return rc;
	}

	public int update(Comment comments) {
		int rc = -1;
		String query1 = "UPDATE Comment SET comment='"+comments.getComment()+"',date='"+comments.getDate()+"' WHERE cid = "+comments.getCid();
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1);
			stmt.close();
		} catch (Exception e) {
			System.err.println("Could not update the Comment: "+ e.getMessage());
		}
		return rc;
	}

	public int delete(int cid) {
		int rc = -1;
		String query = "DELETE FROM Comment WHERE cid=" + cid;

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.err.println("Could not delete Comment: " + e.getMessage());
		}
		return rc;
	}

	private Comment singleWhere(String wClause,boolean forPeople) {
		Comment comments = new Comment();

		ResultSet result;
		String query = buildQuery(wClause,forPeople);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);

			if (result.next()) {
				comments = buildComment(result);
			}
			stmt.close();
		} catch (Exception e) {
			System.err.println("Could not execute select Comment query "+ e.getMessage());
		}
		return comments;
	}

	private ArrayList<Comment> miscWhere(String wClause,boolean forPeople) {
		ArrayList<Comment> comments = new ArrayList<>();

		ResultSet results;
		String query = buildQuery(wClause,forPeople);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				comments.add(buildComment(results));
			}
			stmt.close();
		} catch (Exception e) {
			System.err.println("Could not execute multiple select Comment query "+ e.getMessage());
		}
		return comments;
	}

	private String buildQuery(String wClause, boolean forPeople) {
		String pORa = "CommentPeople";
		if(!forPeople) pORa = "CommentApartment";
		
		String query ="SELECT c.cid,comment,date,author,fname,lname FROM "+pORa+" cp INNER JOIN Comment c ON cp.cid=c.cid INNER JOIN People p ON c.author=p.pid";

		if (wClause.length() > 0) query += " WHERE " + wClause;
		
		return query;
	}

	private Comment buildComment(ResultSet result) {
		Comment comments = new Comment();
		try {
			comments.setCid(result.getInt("cid"));
			comments.setComment(result.getString("comment"));
			comments.setDate(result.getTimestamp("date"));
			comments.setAuthorId(result.getInt("author"));
			comments.setAuthor(result.getString("fname")+" "+result.getString("lname"));

		} catch (Exception e) {
			System.err.println("Could not build the Commment result "+ e.getMessage());
		}
		return comments;
	}
}
